package com.zr.daoimpl;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.zr.dao.DistributionscopeDaoLPN;
import com.zr.model.Distributionrangeprice;
import com.zr.model.Distributionscope;
import com.zr.model.Role;
import com.zr.vo.Dp_DsVO;
import com.zr.vo.Drp_dpVO;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
@Repository("dsDao")
public class DistributionscopeDaoImplLPN extends BaseDaOImpl implements DistributionscopeDaoLPN{

	@Override
	public List findDS(int dpId) {
		
		String sql = "select ds.dsId,ds.dsName,ds.dsDate,ds.dsRemarks,ds.dpId,dp.dpName from distributionscope ds,deliverypoint dp where ds.dpId = dp.dpId and ds.dpId=:dpId";
		Query query = this.getCurrentSession().createSQLQuery(sql);
		query.setInteger("dpId", dpId);
		List<Dp_DsVO>  obj = query.setResultTransformer(Transformers.aliasToBean(Dp_DsVO.class)).list();
		
		return obj;
	}

	@Override
	public JSONArray getDSByUid(int page,int rows,int uid) {
		JSONArray j = new JSONArray();
		String sql = "select  ds.dsId,ds.dsName,ds.dsDate,ds.dsRemarks,dp.dpName  "
				+ "from distributionscope as ds,deliverypoint as dp,employeeinformation as ei "
				+ "where ds.dpId=dp.dpId  and ei.dpId=dp.dpId and  ei.uid = :uid";
		
		Query query = this.getCurrentSession().createSQLQuery(sql);
		query.setInteger("uid", uid);
		query.setFirstResult((page-1)*rows);
		query.setMaxResults(rows);
		List<Dp_DsVO> ss = query.setResultTransformer(Transformers.aliasToBean(Dp_DsVO.class)).list();
		
		
		for (Dp_DsVO dp_DsVO : ss) {
			JSONObject jo = new JSONObject();
			jo.put("dsId", dp_DsVO.getDsId());
			jo.put("dsName", dp_DsVO.getDsName());
			jo.put("dsDate", dp_DsVO.getDsDate());
			jo.put("dsRemarks", dp_DsVO.getDsRemarks());
			jo.put("dpName", dp_DsVO.getDpName());
			j.add(jo);
		}
		return j;
	}

	@Override
	public int getDSCount() {
		String hql = "select count(ds.dsId) from Distributionscope  ds";
    	Query query = this.getCurrentSession().createQuery(hql);
    	int count = ((Number) query.uniqueResult()).intValue();
		return count;
	}

	@Override
	public boolean insertDS(String dsName, String dsRemarks, int dpId, String dsDate) {
		
		boolean flag = false;
		String sql = "insert into distributionscope(dsName,dsRemarks,dpId,dsDate) "
				+ "values(:dsName,:dsRemarks,:dpId,:dsDate)";
		Query query = this.getCurrentSession().createSQLQuery(sql);
		
		query.setString("dsName", dsName);
		query.setString("dsRemarks", dsRemarks);
		query.setString("dsDate", dsDate);
		query.setInteger("dpId", dpId);
		int i = query.executeUpdate();
		if (i>0) {
			flag = true;
		}
		return flag;
	}

	@Override
	public int finddsIdBydpIdAndsName(int dpId, String dsName) {
		int dsId = 0;
		try {
		
		String hql = "from Distributionscope  ds  where ds.deliverypoint.dpId=:dpId and ds.dsName=:dsName";
		
		Query query = this.getCurrentSession().createQuery(hql);
		query.setInteger("dpId", dpId);
		query.setString("dsName",dsName);
		
		List<Distributionscope>  ds = query.list();
		dsId=ds.get(0).getDsId();
		} catch (IndexOutOfBoundsException e) {
			// TODO: handle exception
		}
		return dsId;
	}

	@Override
	public int updateDS(int dsId, String dsName, String dsRemarks, String dsDate) {
		Distributionscope ds = (Distributionscope) this.getCurrentSession().get(Distributionscope.class, dsId);
		ds.setDsName(dsName);
		ds.setDsRemarks(dsRemarks);
		ds.setDsDate(dsDate);
		int i = ds.getDsId();
		return i;
	}

	@Override
	public JSONArray getAllDS(int page,int rows) {
		JSONArray j = new JSONArray();
		String hql = "from Distributionscope";
		Query query = this.getCurrentSession().createQuery(hql);
		query.setFirstResult((page-1)*rows);
		query.setMaxResults(rows);
		List<Distributionscope> dss = query.list();
		for (Distributionscope ds : dss) {
			JSONObject jo = new JSONObject();
			jo.put("dsId",ds.getDsId());
			jo.put("dsName",ds.getDsName());
			jo.put("dsDate", ds.getDsDate());
			jo.put("dsRemarks", ds.getDsRemarks());
			jo.put("dpName",ds.getDeliverypoint().getDpName());
			j.add(jo);
		}
		return j;
	}

}
